/**
 * 
 */
package vehicleService;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import constants.Constants;

/**
 * @author Yokesh Kumar
 * @author Sashiraj Dhanarajan
 *
 */
public class ViewVehicleService {

	private Connection connection;
	private PreparedStatement statement;

	public ViewVehicleService(){
		try {
			Class.forName(Constants.JDBC_DRIVER);
			connection = DriverManager.getConnection(Constants.DB_URL,Constants.USER_NAME,Constants.PWD);
		} 
		catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} 
	}

	/**
	 * This method fetches a list of vehicles available for sale
	 * from saleVehicle table
	 * @return
	 * @throws SQLException 
	 */
	public ResultSet fetchSaleList() throws SQLException{
		ResultSet rs = null;
		String query = "select s.saleVehicleId,s.saleAmount,v.RegNo,v.vehicletype,v.vehicleClass,v.VehicleName from vehicle v,sale_vehicle s where v.vehicleId = s.saleVehicleId and s.soldAmount is null";
		try {
			statement = connection.prepareStatement(query);
			rs =  statement.executeQuery();
		} 
		catch (SQLException e1) {
			e1.printStackTrace();
			throw e1;
		}
		return rs;
	}

	/**
	 * This method fetches the list of vehicle class based on vehicle type
	 * @param vehicleType
	 * @return
	 * @throws SQLException 
	 * @pre Vehicle Class should be selected
	 * @post selects vehicle type based on vehicle class and VehicleId
	 */
	public ResultSet fetchVehicleType(String vehicleClass) throws SQLException{
		ResultSet rs = null;
		String query = "select distinct(v.vehicleType) from vehicle v,rental_vehicle r where v.vehicleClass = ? and v.vehicleId = r.vehicleId";
		try {
			statement = connection.prepareStatement(query);
			statement.setString(1, vehicleClass);
			rs =  statement.executeQuery();
		} 
		catch (SQLException e1) {
			e1.printStackTrace();
			throw e1;
		}
		return rs;
	}

	/**
	 * This method fetches the status of all vehicles of the given type and status
	 * @param vehicleType
	 * @param status
	 * @return Returns the result set with the vehicles based on the type nad status
	 * @throws SQLException 
	 * @pre Network connection should be established
	 * @post Fetches vehicles based on the class, type and status
	 */
	public ResultSet fetchVehicleStatus(String vehicleType,String status) throws SQLException{
		ResultSet rs = null;
		String query = "select v.vehicleId,v.VehicleName,rv.rentId,rv.status "
				+ "from vehicle v inner join rental_vehicle rv"
				+ " where v.vehicleId = rv.vehicleId and v.vehicleType = ? and rv.status = ?";
		;
		try {
			statement = connection.prepareStatement(query);
			statement.setString(1, vehicleType);
			statement.setString(2, status);
			rs =  statement.executeQuery();
		} 
		catch (SQLException e1) {
			e1.printStackTrace();
			throw e1;
		}
		return rs;

	}
	/**
	 * This method fetches the status of all vehicles of the given type
	 * @param vehicleType
	 * @return Returns the resultset based on the vehicle type
	 * @throws SQLException 
	 * @pre Network connection should be established
	 * @post Fetches vehicles with status based on the vehicle type
	 */
	public ResultSet fetchAllVehicleStatus(String vehicleType) throws SQLException{
		ResultSet rs = null;
		String query = "select v.vehicleId,v.vehicleName,rv.rentId,rv.status "
				+ "from vehicle v inner join rental_vehicle rv"
				+ " where v.vehicleId = rv.vehicleId and v.vehicleType = ? ";
		;
		try {
			statement = connection.prepareStatement(query);
			statement.setString(1, vehicleType);
			rs =  statement.executeQuery();
		} 
		catch (SQLException e1) {
			e1.printStackTrace();
			throw e1;
		}
		return rs;

	}
	/**
	 * 
	 * @param VehicleClass
	 * @param status
	 * @return Returns the result set with vehicles based on vehicle class and status
	 * @throws SQLException
	 * @pre Network connection should be established
	 * @post Fetches the vehicles based on vehicle status and vehicle class
	 */
	public ResultSet fetchVehiclesBasedOnStatus(String VehicleClass, String status) throws SQLException {
		ResultSet rs = null;
		String query = "select v.vehicleId,v.vehicleName,rv.rentId,rv.status from vehicle v inner join rental_vehicle rv where v.vehicleId = rv.vehicleId and rv.status = ? and v.vehicleClass= ? ";
		try {
			statement = connection.prepareStatement(query);
			statement.setString(1, status);
			statement.setString(2, VehicleClass);
			rs =  statement.executeQuery();
		} 
		catch (SQLException e1) {
			e1.printStackTrace();
			throw e1;
		}
		return rs;
	}

	public void closeConnection(){
		try{
			if(statement != null){
				statement.close();
			}
			if(connection != null){
				connection.close();
			}
		}
		catch(SQLException sqlExec){
			sqlExec.printStackTrace();
		}
	}


}
